import Database from 'better-sqlite3';
import { fileURLToPath } from 'url';
import { dirname, join } from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const dbPath = join(__dirname, 'food_tracker.db');

// 创建数据库连接
const db = new Database(dbPath);
console.log('Connected to SQLite database.');

// 初始化数据库表
function initializeDatabase() {
  // 用户表
  db.exec(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    nickname TEXT,
    gender TEXT,
    age INTEGER,
    height REAL,
    weight REAL,
    daily_calorie_target INTEGER DEFAULT 2000,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  // 食物表
  db.exec(`CREATE TABLE IF NOT EXISTS foods (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT,
    calories REAL NOT NULL,
    protein REAL NOT NULL,
    carb REAL NOT NULL,
    fat REAL NOT NULL,
    unit TEXT DEFAULT 'g',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  // 饮食记录表
  db.exec(`CREATE TABLE IF NOT EXISTS food_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    food_id INTEGER NOT NULL,
    portion_value REAL NOT NULL,
    portion_unit TEXT DEFAULT 'g',
    record_date DATE NOT NULL,
    meal_type TEXT DEFAULT 'breakfast',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (food_id) REFERENCES foods (id)
  )`);

  // 插入示例食物数据
  insertSampleFoods();
}

// 插入示例食物数据
function insertSampleFoods() {
  const sampleFoods = [
    { name: '苹果', category: '水果', calories: 52, protein: 0.3, carb: 14, fat: 0.2, unit: '个' },
    { name: '香蕉', category: '水果', calories: 89, protein: 1.1, carb: 23, fat: 0.3, unit: '根' },
    { name: '米饭', category: '主食', calories: 130, protein: 2.7, carb: 28, fat: 0.3, unit: '碗' },
    { name: '鸡蛋', category: '蛋白质', calories: 155, protein: 13, carb: 1.1, fat: 11, unit: '个' },
    { name: '鸡胸肉', category: '蛋白质', calories: 165, protein: 31, carb: 0, fat: 3.6, unit: 'g' },
    { name: '牛奶', category: '乳制品', calories: 61, protein: 3.3, carb: 4.8, fat: 3.3, unit: 'ml' },
    { name: '面包', category: '主食', calories: 265, protein: 9, carb: 49, fat: 3.2, unit: '片' },
    { name: '牛肉', category: '蛋白质', calories: 250, protein: 26, carb: 0, fat: 15, unit: 'g' },
    { name: '西兰花', category: '蔬菜', calories: 34, protein: 2.8, carb: 7, fat: 0.4, unit: 'g' },
    { name: '三文鱼', category: '蛋白质', calories: 208, protein: 20, carb: 0, fat: 13, unit: 'g' }
  ];

  const insertStmt = db.prepare(`
    INSERT OR IGNORE INTO foods (name, category, calories, protein, carb, fat, unit) 
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `);

  sampleFoods.forEach(food => {
    insertStmt.run(food.name, food.category, food.calories, food.protein, food.carb, food.fat, food.unit);
  });
}

// 封装数据库操作
const dbRun = (sql, params = []) => {
  const stmt = db.prepare(sql);
  const result = stmt.run(...params);
  return { id: result.lastInsertRowid, changes: result.changes };
};

const dbGet = (sql, params = []) => {
  const stmt = db.prepare(sql);
  return stmt.get(...params);
};

const dbAll = (sql, params = []) => {
  const stmt = db.prepare(sql);
  return stmt.all(...params);
};

// 初始化数据库
initializeDatabase();

export { db, dbRun, dbGet, dbAll };
export default db;